package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.CourseYearInfo;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Course_Year table.
 */
public class CourseYearDb extends BaseDb {
    private String insert = "INSERT INTO course_year (begin_year, end_year) VALUES (?, ?)";
    private String update = "UPDATE course_year SET begin_year = ?, end_year = ? WHERE id = ?";

    /**
     * Insert a Course_Year into database.
     *
     * @param course_year row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(CourseYearInfo course_year) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            st.setString(1, course_year.getStartYear());
            st.setString(2, course_year.getEndYear());
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a Course_Year into database.
     *
     * @param course_year row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(CourseYearInfo course_year) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(course_year);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Course_Year into database.
     *
     * @param courses list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<CourseYearInfo> courses) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (CourseYearInfo course_year : courses) {
                doUpdate(course_year);
            }
        } finally {
            close();
        }
    }

    /**
     * Get a Course_Year for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public CourseYearInfo select(int id) throws SQLException {
        String select = "SELECT * FROM course_year WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Course_Year.
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<CourseYearInfo> select() throws SQLException {
        return select("", "");
    }

    /**
     * Get a list of Course_Year for the given year.
     *
     * @param startYear the row's year to search.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<CourseYearInfo> select(String startYear, String endYear) throws SQLException {
        String select = "SELECT * FROM course_year WHERE begin_year LIKE '%" + startYear + "%' AND end_year LIKE '%" + endYear + "%'";
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<CourseYearInfo> list = new ArrayList<CourseYearInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param course_year the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(CourseYearInfo course_year) throws SQLException {
        st.setString(1, course_year.getStartYear());
        st.setString(2, course_year.getEndYear());
        st.setInt(3, course_year.getId());
        st.executeUpdate();
    }

    private CourseYearInfo getResult() throws SQLException {
        CourseYearInfo course_year = new CourseYearInfo();
        course_year.setId(rs.getInt(1));
        course_year.setStartYear(rs.getString(2));
        course_year.setEndYear(rs.getString(3));

        return course_year;
    }
}
